﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DBManager.UserControls;

namespace DBManager.Service {
    class SqlGenerator {
        public static string SqlForCreateTable(List<ucColumn> pLst, string pTableName) {
            StringBuilder sb = new System.Text.StringBuilder();
            sb.AppendFormat("CREATE TABLE [dbo].[{0}](", pTableName).AppendLine();
            sb.AppendLine("[ID] [bigint] IDENTITY(1,1) NOT NULL");
            foreach (ucColumn item in pLst) {
                sb.AppendFormat(",{0} {1} NULL", item.ColumnName, item.FullColumnType);
            }
            sb.AppendLine(",[CreateOn] [datetime] NULL").AppendLine();
            sb.AppendLine(",[CreateBy] [bigint] NULL").AppendLine();
            sb.AppendLine(",[CreateByName] [nvarchar](50) NULL").AppendLine();
            sb.AppendLine(",[UpdateOn] [datetime] NULL").AppendLine();
            sb.AppendLine(",[UpdateBy] [bigint] NULL").AppendLine();
            sb.AppendLine(",[UpdateByName] [nvarchar](50) NULL").AppendLine();
            sb.AppendFormat(", CONSTRAINT [PrimaryKey_{0}] PRIMARY KEY CLUSTERED ", pTableName).AppendLine();
            sb.AppendLine("([ID] ASC");
            sb.AppendLine(")WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]");
            sb.AppendLine(") ON [PRIMARY]");

            return sb.ToString();
        }
        private static string SqlForCreateSP(List<ucColumn> pLst, string pTableName) {
            StringBuilder sb = GetMainBodyStringBuilder(pTableName, 0);
            StringBuilder sbParameter = new StringBuilder();
            StringBuilder sbColums = new StringBuilder();
            StringBuilder sbValues = new StringBuilder();

            // Build SP
            sbParameter.AppendFormat("    @{0} {1}", pLst[0].ColumnName, pLst[0].FullColumnType).AppendLine();
            sbColums.AppendFormat(pLst[0].ColumnName);
            sbValues.AppendFormat("@{0}", pLst[0].ColumnName);
            for (int i = 1; i < pLst.Count(); i++ ) {
                var item = pLst[i];
                sbParameter.AppendFormat("   ,@{0} {1}", item.ColumnName, item.FullColumnType).AppendLine();
                sbColums.AppendFormat(",{0}", item.ColumnName);
                sbValues.AppendFormat(",@{0}", item.ColumnName);
            }
            sbParameter.AppendLine("   ,@CreateBy INT").Append("   ,@CreateByName nvarchar(50)");
            sbColums.Append(",CreateOn,CreateBy,CreateByName,UpdateOn,UpdateBy,UpdateByName");
            sbValues.Append(",GetDate(),@CreateBy,@CreateByName,GETDATE(),@CreateBy,@CreateByName");

            sb.AppendLine(sbParameter.ToString());
            sb.AppendLine("AS");
            sb.AppendLine("Begin");
            sb.AppendFormat("Insert {0}(", pTableName);
            sb.Append(sbColums.ToString()).AppendLine(")");

            sb.Append("Values(");
            sb.Append(sbValues.ToString()).AppendLine(")");
            sb.AppendLine("End");
            sb.AppendLine("GO");

            return sb.ToString();
        }

        private static string SqlForUpdateSP(List<ucColumn> pLst, string pTableName) {
            StringBuilder sb = GetMainBodyStringBuilder(pTableName, 1);
            StringBuilder sbParameter = new StringBuilder();
            StringBuilder sbUpdateBody = new StringBuilder();

            // Build SP
            sbParameter.AppendFormat("    @ID BIGINT", pLst[0].ColumnName, pLst[0].FullColumnType).AppendLine();
            sbParameter.AppendFormat("    ,@{0} {1}", pLst[0].ColumnName, pLst[0].FullColumnType).AppendLine();
            sbUpdateBody.AppendFormat("   Set {0} = @{0}", pLst[0].ColumnName).AppendLine();
            for (int i = 1; i < pLst.Count(); i++) {
                var item = pLst[i];
                sbParameter.AppendFormat("   ,@{0} {1}", item.ColumnName, item.FullColumnType).AppendLine();
                sbUpdateBody.AppendFormat("   ,{0} = @{0}", item.ColumnName).AppendLine();
            }
            sbParameter.AppendLine("   ,@UpdateBy INT").Append("   ,@UpdateByName nvarchar(50)");
            sbUpdateBody.AppendLine("   ,UpdateBy=@UpdateBy").AppendLine("   ,UpdateByName = @UpdateByName");
            sbUpdateBody.Append("   Where ID = @ID");
            sb.AppendLine(sbParameter.ToString());
            sb.AppendLine("AS");
            sb.AppendLine("Begin");
            sb.AppendFormat("Update {0}", pTableName).AppendLine();
            sb.AppendLine(sbUpdateBody.ToString());
            sb.AppendLine("End");
            sb.AppendLine("GO");

            return sb.ToString();
        }

        private static string SqlForDeleteSP(string pTableName) {
            StringBuilder sb = GetMainBodyStringBuilder(pTableName, 2);
            sb.AppendLine("    @ID bigint");
            sb.AppendLine("AS");
            sb.AppendLine("Begin");
            sb.AppendLine("    Delete From " + pTableName);
            sb.AppendLine("    Where ID = @ID");
            sb.AppendLine("End");
            sb.AppendLine("GO");

            return sb.ToString();
        }

        private static StringBuilder GetMainBodyStringBuilder(string pTableName, int pType) {
            string opType = string.Empty;
            switch (pType) { 
                case 0:
                    opType = "Insert";
                    break;
                case 1:
                    opType = "Update";
                    break;
                case 2:
                    opType = "Delete";
                    break;
            }
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("IF  OBJECT_ID(N'sp_{0}_{1}') IS NOT NULL ", opType, pTableName).AppendLine();
            sb.AppendFormat("Drop Procedure sp_{0}_{1}", opType, pTableName).AppendLine();
            sb.AppendFormat("GO").AppendLine();
            sb.AppendFormat("Create Procedure sp_{0}_{1}", opType, pTableName).AppendLine();
            return sb;
        }

        public static string GetSPString(List<ucColumn> pLst, string pTableName) {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine(SqlForCreateSP(pLst, pTableName));
            sb.AppendLine(SqlForUpdateSP(pLst, pTableName));
            sb.AppendLine(SqlForDeleteSP(pTableName));

            return sb.ToString();
        }
    }
}
